﻿using System;
using System.Linq;
using System.Collections.Generic;

using LeanEngine.Entity;

namespace LeanEngine.Data.SqlClient
{
    internal sealed class SqlClientTextBuilder : ISqlTextBuilder
    {
        #region ICrudSqlBuilder Members

        public string BuildInsertCommand(string tabelName, IEnumerable<MetaColumn> columnsList)
        {
            var parameters = string.Empty;
            var columns = string.Empty;

            foreach (var column in columnsList.WhereNotDbGenerated())
            {
                foreach (var item in column.DBColumn.Name.Split('|'))
                {
                    columns += item + ", ";
                    parameters += "@" + item + ", ";
                }
            }

            var sqlUpdate = "INSERT INTO " + tabelName + " (" +
                columns.Substring(0, columns.Length - 2) + ") VALUES (" +
                parameters.Substring(0, parameters.Length - 2) + "); SELECT SCOPE_IDENTITY();";

            return sqlUpdate;
        }

        public string BuildDeleteCommand(string tableName, IEnumerable<MetaColumn> pkColumnsList)
        {
            var parameters = string.Empty;
            var sqlDelete = "DELETE FROM " + tableName + " WHERE ";
            foreach (var column in pkColumnsList)
            {
                parameters += column.DBColumn.Name + " = @" + column.DBColumn.Name + " AND ";
            }

            return parameters.Length < 3 ? string.Empty : sqlDelete + parameters.Substring(0, parameters.Length - 5);
        }

        public string BuildReadCommand(string tableName, IEnumerable<MetaColumn> pkColumnsList)
        {
            var parameters = string.Empty;
            var sqlDelete = "SELECT * FROM " + tableName + " WHERE ";
            foreach (var column in pkColumnsList)
            {
                parameters += column.DBColumn.Name + " = @" + column.DBColumn.Name + " AND ";
            }

            return parameters.Length < 3 ? string.Empty : sqlDelete + parameters.Substring(0, parameters.Length - 5);
        }

        public string BuildListCommand(string tableName)
        {
            return "SELECT * FROM " + tableName;
        }

        public string BuildPagedListCommand(string tableName, IEnumerable<MetaColumn> pkColumnsList)
        {
            var sql = "WITH result_set AS \r\n( \r\n\t " +
                "SELECT *, ROW_NUMBER() OVER(ORDER BY ";
            foreach (var column in pkColumnsList)
            {
                foreach(var columnName in column.DBColumn.Name.Split('|'))
                {
                    sql += columnName + ", ";
                }
            }

            sql = sql.Substring(0, sql.Length - 2);

            sql += ") AS row_number\r\n\t" + 
                "FROM " + tableName + "\r\n)\r\n" +
                "SELECT *\r\n FROM result_set\r\n WHERE row_number BETWEEN @PageIndex AND @PageSize";

            return sql;
        }

        public string BuildCountCommand(string tableName)
        {
            return "SELECT COUNT(*) FROM " + tableName;
        }

        #endregion
    }
}
